package com.esunrising.clickhouse.controller;

import com.alibaba.fastjson.JSONObject;
import com.clickhouse.jdbc.ClickHouseDataSource;
import com.esunrising.clickhouse.dto.emp;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@RestController
@RequestMapping("/index")
public class IndexController {
    @GetMapping("/index")
    public String index(){
        return "welcome esun";
    }


    @GetMapping("/ch")
    public String clickhouse(){
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
// optionally set connection properties
//        properties.setProperty("client_name", "Agent #1");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                System.out.println("连接成功");
            }else{
                System.out.println("连接失败");
            }
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select county, price from uk_price_paid limit 10");
            while (rs.next()){
                String county = rs.getString("county");
                double price = rs.getDouble("price");
                System.out.println("county: "+county+"  , price:"+price);
            }



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return "连接成功";
    }

    @GetMapping("/chwithuser")
    public String clickhouseWithUser(){
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                System.out.println("连接成功");
            }else{
                System.out.println("连接失败");
            }
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select county, price from uk_price_paid limit 10");
            while (rs.next()){
                String county = rs.getString("county");
                double price = rs.getDouble("price");
                System.out.println("county: "+county+"  , price:"+price);
            }



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return "连接成功";
    }

    @GetMapping("/select")
    public JSONObject clickhouseSelect(){
        JSONObject result = new JSONObject();
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                String sql = "select id, name, hiredate, salary from emp";
                pstmt = conn.prepareStatement(sql);

                rs = pstmt.executeQuery();

                List<emp> empList = new ArrayList<>();
                while (rs.next()){
                    empList.add(new emp(rs.getInt("id"),rs.getString("name"), rs.getDate("hiredate"), rs.getDouble("salary")));
                }

                result.put("code", 200);
                result.put("msg", "查询成功");
                result.put("size", empList.size());
                result.put("data", empList);
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }



        }catch (Exception e){
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return result;
    }

    @PostMapping("/insert")
    public JSONObject clickhouseInsert(){
        JSONObject result = new JSONObject();
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                String sql = "insert into emp(id, name, hiredate, salary) values (?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, 2);
                pstmt.setString(2, "李四");
                pstmt.setDate(3, Date.valueOf("2018-01-01"));
                pstmt.setDouble(4, 6123.50);
                boolean res = pstmt.execute();
                result.put("code", 200);
                result.put("msg", "添加成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        }catch (Exception e){
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return result;
    }

    @PostMapping("/insert2")
    public JSONObject clickhouseInsert2(@RequestBody List<emp> empList){
        JSONObject result = new JSONObject();
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                String sql = "insert into emp(id, name, hiredate, salary) values (?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                for (emp emp : empList) {
                    pstmt.setInt(1, emp.getId());
                    pstmt.setString(2, emp.getName());
                    pstmt.setDate(3, emp.getHiredate());
                    pstmt.setDouble(4, emp.getSalary());
                    boolean res = pstmt.execute();
                }
                result.put("code", 200);
                result.put("msg", "添加成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        }catch (Exception e){
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return result;
    }

    @PostMapping("/delete")
    public JSONObject clickhouseDelete(@RequestBody String param){
        JSONObject jo = JSONObject.parseObject(param);
        String idStr = jo.getString("idStr");

        JSONObject result = new JSONObject();
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                String sql = "alter table emp delete where id = ?";
                pstmt = conn.prepareStatement(sql);
                String[] idArr = idStr.split(",");
                for (String id : idArr) {
                    if(id != null && id.length() > 0) {
                        pstmt.setInt(1, Integer.valueOf(id));
                        pstmt.execute();
                    }
                }
                result.put("code", 200);
                result.put("msg", "删除成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        }catch (Exception e){
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return result;
    }

    @PostMapping("/update")
    public JSONObject clickhouseUpdate(@RequestBody List<emp> empList){
        JSONObject result = new JSONObject();
        // jdbc:clickhouse://{host::localhost}?[:{port::8123}][/{database}?][\?<&,user={user},password={password},{:identifier}={:identifier}>]
        String url = "jdbc:clickhouse://192.168.3.110?8123/default";
        Properties properties = new Properties();
        properties.setProperty("user", "esun");
        properties.setProperty("password", "123456");


        ClickHouseDataSource dataSource = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            dataSource = new ClickHouseDataSource(url, properties);
            conn = dataSource.getConnection();
            if(conn != null){
                String sql = "alter table emp update name = ?, hiredate = ?, salary = ? where id = ?";
                pstmt = conn.prepareStatement(sql);
                for (emp emp : empList) {
                    pstmt.setString(1, emp.getName());
                    pstmt.setDate(2, emp.getHiredate());
                    pstmt.setDouble(3, emp.getSalary());
                    pstmt.setInt(4, emp.getId());
                    int res = pstmt.executeUpdate();
                    System.out.println("res:"+res);
                }
                result.put("code", 200);
                result.put("msg", "更新成功");
            }else{
                result.put("code", 500);
                result.put("msg", "数据库连接失败");
            }
        }catch (Exception e){
            e.printStackTrace();
            result.put("code", 500);
            result.put("msg", "发生错误："+e.getMessage());
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(pstmt != null){
                try {
                    pstmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

        return result;
    }
}
